package edu.byu.intex2.data.dao;

import edu.byu.intex2.data.Cache;
import edu.byu.intex2.data.ConnectionPool;
import edu.byu.intex2.data.DataException;
import edu.byu.intex2.data.GUID;
import edu.byu.intex2.data.bo.OrderLine;
import java.sql.*;

/**
 * OrderLine Data Access Object
 * @author Brady
 */
public class OrderLineDAO {

    /////////////////////////////////////////////
    ///   Singleton code
    private static OrderLineDAO instance = null;

    /** Creates a new instance of OrderLineDAO */
    private OrderLineDAO() {
    }//constructor

    /** Retrieves the single instance of this class */
    public static synchronized OrderLineDAO getInstance() {
        if (instance == null) {
            instance = new OrderLineDAO();
        }
        return instance;
    }//getInstance

    ////////////////////////////////////////////
    ///   CREATE methods
    /**
     * Creates a new orderLine in the database
     * @param id GUID
     * @param partId id of part
     */
    public OrderLine create(String orderId, String partId) throws DataException {
        OrderLine orderLine = new OrderLine(GUID.generate()); // generate a guid for the ordersLine even though we never use this...
        orderLine.setObjectAlreadyInDB(false);

        // Set Order ID
        orderLine.setOrderId(orderId);

        // Set Part
        orderLine.setPartId(partId);

        Cache.getInstance().put(orderLine.getId(), orderLine); // put in cache just in case...
        return orderLine;
    }//create


    ////////////////////////////////////////////
    ///   READ methods
    /**
     * Reads an existing orderLine from the database     */
    public OrderLine read(String orderId, String partId) throws DataException {
        // Check Cache -- can't with an orderLine
//        if (Cache.getInstance().containsKey(id)) {
//            return (OrderLine) Cache.getInstance().get(id);
//        }

        // Get Connection
        Connection conn = ConnectionPool.getInstance().get();

        // Try Reading with connection
        try {
            return read(orderId, partId, conn);
        } catch (Exception e) {
            throw new DataException("An error occurred while reading the OrderLine object information.", e);
        } finally {
            ConnectionPool.getInstance().release(conn);
        }
    }

    /**
     * Internal method to read an existing orderLine from the database
     * @return orderLine
     */
    synchronized OrderLine read(String orderId, String partId, Connection conn) throws Exception {
        // Check Cache
//        if (Cache.getInstance().containsKey(orderId)) {
//            return (OrderLine) Cache.getInstance().get(orderId);
//        }

        // Prepare a SQL Statement
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM order_line where order_id=? AND part_id=?");

        try {
            stmt.setString(1, orderId);
            stmt.setString(2, partId);
            ResultSet rs = stmt.executeQuery(); // execute query
            if (rs.next()) {
                // Read the record with the result set
                return readRecord(rs, conn);
            }//if
            throw new DataException("OrderLine with orderId '" + orderId + "' not found.");
        } finally {
            stmt.close();
        }
    }//read

    /** Internal method to create a orderLine object from a record  */
    synchronized OrderLine readRecord(ResultSet rs, Connection conn) throws Exception {
        if (Cache.getInstance().containsKey(rs.getString("id"))) {
            return (OrderLine) Cache.getInstance().get(rs.getString("id"));
        }
        // Create new orderLine object
        OrderLine orderLine = new OrderLine(rs.getString("id"));

        // OrderLine is already in DB
        orderLine.setObjectAlreadyInDB(true);

        // Put orderLine in cache
        Cache.getInstance().put(orderLine.getId(), orderLine);

        // Set OrderLine Attributes
        // TODO: Assign Correct Orderline Attributes
        //      Date: 3/4/2009 - Brady White
        orderLine.setOrderId(rs.getString("order_id"));
        orderLine.setPartId(rs.getString("physical_car_id"));
//        orderLine.setActualPrice(rs.getDouble("actual_price"));
//        orderLine.setListPrice(rs.getDouble("list_price"));

        // OrderLine is dirty and needs to be saved to DB
        orderLine.setDirty(false);

        return orderLine;
    }//readRecord


    /////////////////////////////////////////////
    ///   UPDATE methods
    /** Saves an existing orderLine in the database */
    public void save(OrderLine orderLine) throws DataException {
        // get a connection from the pool, save all of the orders first using the other orderLine method and then commit them in the database
        Connection conn = ConnectionPool.getInstance().get();

        try {
            save(orderLine, conn); // save with connection
            conn.commit();  // commit the db transaction
        } catch (Exception e) {
            try {
                //rollback if there were any errors saving, catch any other errors you might have
                conn.rollback();
            } catch (SQLException e2) {
                throw new DataException("Could not roll back the orderLine database transaction!", e2);
            }
            throw new DataException("An error occurred while saving the orderLine object information.", e);
        } finally {
            // release the connection
            ConnectionPool.getInstance().release(conn);
        }
    }

    /** Internal method to update a orderLine in the database */
    void save(OrderLine orderLine, Connection conn) throws Exception {
        // put the orderLine object to cache
        Cache.getInstance().put(orderLine.getId(), orderLine);

        // decide whether to update or insert into the database
        if (orderLine.isObjectAlreadyInDB() && orderLine.isDirty()) {
            update(orderLine, conn);
        } else if (orderLine.isDirty()) {
            insert(orderLine, conn);
        }//if
    }

    /** External method to update or create a orderLine in the database without committing connection */
    public void saveNoCommit(OrderLine orderLine, Connection conn) throws Exception {
        // Get Already in Databsae
         Boolean alreadyInDB = orderLine.isObjectAlreadyInDB();

        // decide whether to update or insert into the database
        if (alreadyInDB) {
            update(orderLine, conn);
        } else {
            insert(orderLine, conn);
        }

        // Save No Commit our pretty part
        PartDAO.getInstance().saveNoCommit(orderLine.getPart(), conn);

        // TODO: Set Lot to null
    }

    /** Saves an existing orderLine to the database */
    private void update(OrderLine orderLine, Connection conn) throws Exception {
        // use a prepared statement to prevent sql injection
        // TODO: Correct Update Statement
        //      Date: 3/4/2009 - Brady White
        PreparedStatement stmt = conn.prepareStatement("UPDATE order_line SET actual_price=?, list_price=?  WHERE order_id=? AND physical_car_id=?");
        try {
            // Get OrderLine Attributes and add them to the statement
            // TODO: Assign Correct SQL Orderline Attributes
            //      Date: 3/4/2009 - Brady White
            stmt.setString(1, orderLine.getOrderId());
            stmt.setString(2, orderLine.getPartId());
            stmt.execute();

            // OrderLine is clean!
            orderLine.setDirty(false);
        } finally {
            stmt.close();  // make sure to close the prepared statement
        }
    }

    /** Inserts a new orderLine into the database */
    private void insert(OrderLine orderLine, Connection conn) throws Exception {
        // TODO: Correct Insert Statment
        //      Date: 3/4/2009 - Brady White
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO order_line (order_id, physical_car_id, actual_price, list_price) VALUES (?, ?, ?, ?)");
        try {
            // Set OrderLine Attributes
            // TODO: Assign Correct Orderline Attributes
            //      Date: 3/4/2009 - Brady White
            stmt.setString(1, orderLine.getOrderId());
            stmt.setString(2, orderLine.getPartId());
            stmt.execute();

            orderLine.setObjectAlreadyInDB(true); // now in DB
            orderLine.setDirty(false); // object is clean
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            stmt.close(); // close the prepared statement
        }
    }

    /////////////////////////////////////////////////
    ///   DELETE methods
    /** We do not support deleting of business objects in this application */
    public void delete(OrderLine orderLine) throws DataException {
        throw new RuntimeException("Nice try. The delete function is not supported in this application.");
    }

    ////////////////////////////////////////////////
    ///   SEARCH methods

}
